﻿CREATE TRIGGER [TRG_Data_Delete]
ON [dbo].[Data]
INSTEAD OF DELETE
AS
BEGIN
	SET NOCOUNT ON

	DECLARE @i INT

	BEGIN TRY

		BEGIN TRAN

		-- Get all children
		;WITH DataChildrenCte AS
		(
			SELECT DataId, ParentDataId, 1 as Lvl
			FROM [dbo].[Data]
			WHERE DataId = (SELECT DataId FROM deleted)

			UNION ALL

			SELECT D.DataId, D.ParentDataId, M.Lvl + 1
			FROM [dbo].[Data] D  
			INNER JOIN DataChildrenCte M ON M.DataId = D.ParentDataId
		)
		SELECT DataId, Lvl
		INTO #t
		From DataChildrenCte

		DELETE FROM [dbo].[DataMapping]
		WHERE [SourceDataId] in (SELECT [DataId] FROM #t)

		DELETE FROM [dbo].[DataMapping]
		WHERE [TargetDataId] in (SELECT [DataId] FROM #t)

		SELECT @i = MAX(Lvl)
		FROM #t

		WHILE @i > 0
		BEGIN
			DELETE FROM [dbo].[Data] WHERE DataId IN (SELECT DataId FROM #t WHERE Lvl = @i)
			SET @i = @i -1
		END

		DROP TABLE #t

		IF @@TRANCOUNT > 0
			COMMIT TRAN;
	END TRY
	BEGIN CATCH
		IF @@TRANCOUNT > 0
			ROLLBACK TRAN;
		THROW;
	END CATCH
END

